    /*
 * DatabaseInterface.java
 *
 * Created on July 24, 2006, 2:51 PM
 *
 * @author Francois Daniels
 */

package za.gateway.io;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Properties;


/**
 * This class is responsible for connecting to the database. It will be 
 * concerned with issues such as the creation of connection object, 
 * initialising the JBDC driver and handling any exceptions which 
 * occur.
 * 
 * It will provide two public functions. Update and Query. Their 
 * functions are analogous to their names.
 *
 * @author Francois Daniels
 */
public class DatabaseInterface {
    /** Variables belonging to the DatabaseInterface class */
    
    /** 
     * This will contain the connection to the database with which 
     * query and updates with be performed. 
     */
    protected Connection DB_Connection;
    
    /** 
     * This will contain the name of the user that we would like to 
     * connect to the database as. 
     */
    protected String User;
    
    /** 
     * This will contain the current user's database password. 
     */
    protected String Password;
    
    /** 
     * This will contain the address for the database server. 
     */
    protected String Host;
    
    /** 
     * This will contain the name of the database within the server. 
     */
    protected String Database;
    
    /**
     * This function will open a new connection to the database. 
     * If the connection is established correctly it will return that 
     * connection, else it will return NULL.
     *
     * @return A new connection to the database, or NULL if an error 
     * occurs.
     */
    protected java.sql.Connection Connect () {
        String Url = "";
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception Ex) {
            Log.ERROR (Ex);
            return null;
        } 
        
        // We connect to the database using the provided variables
        try {
            Url = "jdbc:mysql://" + Host + "/" + Database;
            Properties Info = new Properties ();
            Info.setProperty ("user", User);
            Info.setProperty ("password", Password);
            Connection con = DriverManager.getConnection (Url, Info);
            return con;
        } catch (java.sql.SQLException E) {
            Log.ERROR (E);
        }
        return null;
    }
    
    /**
     * Closes DB_Connection. This will also close any open statements.
     */
    public void Close () {
        try {
            DB_Connection.close ();
        } catch (Exception E) {
            Log.ERROR (E);
        }
    }
    
    /**
     * This function will open a new connection to the database. It 
     * will then execute the update to the database. When this has been 
     * completed it will close the connection to the database.
     *
     * @param UpdateInstruction     The SQL update command.
     * @return                      true if the command succeed, false 
     *                              otherwise.
     **/
    public boolean Update (String UpdateInstruction) {
        DB_Connection = Connect ();
        
        if (DB_Connection == null) return false;
        
        try {
            // We execute the query
            Statement S = DB_Connection.createStatement ( );
            S.executeUpdate ( UpdateInstruction );
            S.close ( );
        } catch (Exception E)
        {Log.ERROR (E); return false;}
        Close ();
        return true;
    }
    
    /**
     * This function will open a new connection to the database. It 
     * will execute the query and return the java.sql.ResultSet to the 
     * caller of the function.
     *
     * @param QueryInstruction  This should contain a valid SQL select 
     *                          query.
     * @return                  The result of the query or <b>null</b> 
     *                          if an error occurs during execution.
     **/
    public ResultSet Query (String QueryInstruction) {
        Connection Con = Connect ();
        ResultSet Results = null;
        
        if (Con == null) return null;
        
        try {
            // We excecute the query
            Statement Statemnt = Con.createStatement ();
            Results = Statemnt.executeQuery (QueryInstruction);
        } catch (Exception E)
        {Log.ERROR (E);}
        
        // We return the results from this execution
        return Results;
    }
    
    /** Functions belonging to the DatabaseInterface class */
    
    /**
     * Creates a new instance of DatabaseInterface
     *
     * @param Username      The username with which to log into the 
     *                      MySQL server.
     * @param Passwd        The password with which to log into the 
     *                      MySQL server.
     * @param HostName      The MySQL server's hostname, or its IP 
     *                      address.
     * @param DatabaseName  The name of the database on the MySQL 
     *                      server into which we would like to log 
     *                      into.
     */
    public DatabaseInterface (String Username, String Passwd, 
            String HostName, String DatabaseName) {
        
        DB_Connection   = null;
        User            = Username;
        Password        = Passwd;
        Host            = HostName;
        Database        = DatabaseName;
    }
    
    /**
     * This function closes a Connection object.
     *
     * @param Con This is the connection which we would like to close.
     */
    private void Close (Connection Con) {
        try {
            if (Con != null)
                Con.close ();
        } catch (Exception E)
        {Log.ERROR (E);}
    }
    
    /**
     * This function will close a Statement object.
     *
     * @param S This is the statement which we would like to close.
     */
    private void close (Statement S) {
        try {
            if (S != null)
                S.close ();
        } catch (Exception E)
        {Log.ERROR (E);}
    }
}
